package integrationTier.sql;

import core.MovieException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import core.Medium;
import integrationTier.IMediumDAO;

/**
 * SQL DAO objekt k mediim
 * @author vojta
 */
public class MediumSQLDAO  implements IMediumDAO{

    Statement stmt;

    public MediumSQLDAO (Connection cn) throws MovieException{
        try {
            stmt = cn.createStatement();
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }
    }

    /**
     * Vypise vsechny media
     * @return ArrayList
     * @throws MovieException
     */
    public List<Medium> listAll() throws MovieException {
        List<Medium> list = new ArrayList<Medium>();
        String q = "SELECT * FROM \"media\"";
        try {
            ResultSet rs = stmt.executeQuery(q);

            while (rs.next()) {
                list.add(new Medium(rs.getInt("id"), rs.getString("medium")));
            }
            return list;
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }
    }

    /**
     * Ziska dane medium
     * @param medium
     * @return Medium
     * @throws MovieException
     */
    public Medium get(Medium medium) throws MovieException {
        try {
            int id = medium.getId();
            String q = "SELECT * FROM \"media\" WHERE \"id\" = " + id + "";
            ResultSet rs = stmt.executeQuery(q);
            rs.next();
            Medium m = new Medium(id, rs.getString("medium"));
            return m;
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }
    }

    /**
     * Prida nove medium
     * @param medium
     * @throws MovieException
     */
    public void add(Medium medium) throws MovieException {
        try {
            String q = "INSERT INTO \"media\"(\"medium\") VALUES('"+ medium.getMedium() +"')";
            stmt.executeUpdate(q);
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }
    }

    /**
     * Zjistuje, jestli je dane medium pouzite
     * @param medium
     * @return boolean
     * @throws MovieException
     */
    private boolean isUsed(Medium medium) throws MovieException {
        try {
            String q = "SELECT * FROM \"movies\" WHERE \"medium_id\" = " + medium.getId() + "";
            ResultSet rs = stmt.executeQuery(q);
            rs.next();

            rs.getString("id");
            return true;
        } catch (SQLException ex) {
            return false;
        }

    }

    /**
     * Odstrani vybrane medium
     * @param medium
     * @throws MovieException
     */
    public void remove(Medium medium) throws MovieException {
        try {
            if (!isUsed(medium)) {
                String q = "DELETE FROM \"media\" WHERE \"id\" = " + medium.getId() + "";
                stmt.executeUpdate(q);
            }
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }
    }

    /**
     * Zmeni medium
     * @param medium
     * @throws MovieException
     */
    public void change(Medium medium) throws MovieException {
        try {
            String q = "UPDATE \"media\" SET \"medium\" = '" + medium.getMedium() + "' WHERE \"id\" = " + medium.getId() + "";
            stmt.executeUpdate(q);
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }
    }

    /**
     * Uzavreni spojeni s DB
     * @throws MovieException
     */
    public void close() throws MovieException {
        try {
            stmt.execute("SHUTDOWN");
            stmt.close();
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }
    }    

}
